package com.softlook.mt.server.db.dao;

import java.util.List;
import java.util.LinkedList;
import java.util.Hashtable;
import java.lang.Integer;
import java.lang.StringBuffer;
import jp.crossfire.framework.database.ICondition;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import jp.crossfire.framework.database.SQLExecutor;
import com.softlook.mt.server.db.internal.GpsdataManagerGetListner;
import jp.crossfire.framework.database.ConditionContainer;
import jp.crossfire.framework.database.Condition;
import jp.crossfire.framework.database.Values;
import com.softlook.mt.server.db.internal.GpsdataManagerGetLastListner;
import com.softlook.mt.server.db.internal.GpsdataManagerInternal;

public class GpsdataManager extends GpsdataManagerInternal {
	private List gpsdata = new LinkedList();
	public List getGpsdata() {
		return this.gpsdata;
	}
	public void setGpsdata(List gpsdata) {
		this.gpsdata = gpsdata;
	}
	public String getSql(Hashtable input, Integer limit, Integer offset) {
		StringBuffer buf = new StringBuffer();
		
		buf.append("SELECT ");
		/*buf.append("gpsdata.gaslevel");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.distance");
		buf.append(",");
		buf.append(" ");*/
		buf.append("gpsdata.label");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.lac");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.alt");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.cell");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.gpsstatus");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.ssi");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.mnc");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.mcc");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.time");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.hdop");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.course");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.speed");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.lon");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.lat");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.imei");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.deviceid");
		buf.append(",");
		buf.append(" ");
		buf.append("gpsdata.gpsdataid");
		
		buf.append(" FROM ");
		buf.append("gpsdata");
		
		// Condition
		ICondition condition = getGetCondition(input);
		if(condition != null && !condition.toString().equals("")){
			buf.append(" WHERE " + condition.toString());
		}
		
		// GROUP BY
		
		// Having Condition
		condition = getGetHavingCondition(input);
		if(condition != null && !condition.toString().equals("")){
			buf.append(" HAVING " + condition.toString());
		}
		
		// ORDER
		
		// Limit
		if(limit != null && offset != null){
			buf.append(" LIMIT " + offset.toString() +", " + limit.toString());
		}
		
		return buf.toString();
	}
	public List get(Connection con, Hashtable input, Integer limit, Integer offset) throws jp.crossfire.framework.database.HyperDbException {
		this.reset();
		String sql = getSql(input, limit, offset);
		SQLExecutor exec = new SQLExecutor();
		exec.setCon(con);
		
		// Listners
		List listners = new LinkedList();
		
		GpsdataManagerGetListner listner0 = (new GpsdataManagerGetListner());
		listners.add(listner0);
		
		// Execute Query
		exec.executeSelectSQL(sql, listners);
		return listner0.result;
	}
        public List getRaw(Connection con, Hashtable input, Integer limit, Integer offset) throws Exception {
            LinkedList list = new LinkedList();
            try{
		this.reset();
		String sql = getSql(input, limit, offset);
                Statement stmt = con.createStatement();
                System.out.println("[RAW] execute get gpsdata select SQL -> " + sql);

                stmt.executeQuery(sql);
                ResultSet result = stmt.getResultSet();
                while(result.next()){
                    if (result.getObject("LAT")!=null && result.getObject("LON")!=null) {
                        list.add(new float[] {result.getFloat("LAT"), result.getFloat("LON")});
                    }
                }

                //result.close();
                stmt.close();

                return list;
                
            }catch(Exception e){
                throw new Exception(e);
            }

        }
	public String setSql(Hashtable input) {
		StringBuffer buf = new StringBuffer();
		
		buf.append("UPDATE ");
		buf.append("gpsdata");
		
		// SET
		ICondition cnd0= new ConditionContainer(",");
		Condition cnd1 = new Condition("deviceid = '$deviceid'");
		if(input.get("deviceid") != null){
			((Condition)cnd1).getProperties().put("deviceid", input.get("deviceid"));
		}
		((ConditionContainer)cnd0).addChild(cnd1);
		if(!cnd0.toString().equals("")){
			buf.append(" SET " + cnd0.toString());
		}
		
		// Condition
		ICondition condition = setGetCondition(input);
		if(condition != null && !condition.toString().equals("")){
			buf.append(" WHERE " + condition.toString());
		}
		
		return buf.toString();
	}
	public void set(Connection con, Hashtable input) throws jp.crossfire.framework.database.HyperDbException {
		String sql = setSql(input);
		SQLExecutor exec = new SQLExecutor();
		exec.setCon(con);
		
		exec.execUpdateSQL(sql);
	}
	public String addSql(Hashtable input) {
		StringBuffer buf = new StringBuffer();
		
		buf.append("INSERT INTO ");
		buf.append("gpsdata");
		
		ConditionContainer cnd0= new Values("VALUES");
		ConditionContainer cnd1 = new ConditionContainer(",");
		ConditionContainer cnd2 = new ConditionContainer(",");
		cnd0.addChild(cnd1);
		cnd0.addChild(cnd2);
		Condition cnd3 = new Condition("deviceid");
		cnd1.addChild(cnd3);
		Condition cnd4 = new Condition("'$deviceid'");
		if(input.get("deviceid") != null){
			((Condition)cnd4).getProperties().put("deviceid", input.get("deviceid"));
		}
		cnd2.addChild(cnd4);
		Condition cnd5 = new Condition("imei");
		cnd1.addChild(cnd5);
		Condition cnd6 = new Condition("'$imei'");
		if(input.get("imei") != null){
			((Condition)cnd6).getProperties().put("imei", input.get("imei"));
		}
		cnd2.addChild(cnd6);
		Condition cnd7 = new Condition("lat");
		cnd1.addChild(cnd7);
		Condition cnd8 = new Condition("'$lat'");
		if(input.get("lat") != null){
			((Condition)cnd8).getProperties().put("lat", input.get("lat"));
		}
		cnd2.addChild(cnd8);
		Condition cnd9 = new Condition("lon");
		cnd1.addChild(cnd9);
		Condition cnd10 = new Condition("'$lon'");
		if(input.get("lon") != null){
			((Condition)cnd10).getProperties().put("lon", input.get("lon"));
		}
		cnd2.addChild(cnd10);
		Condition cnd11 = new Condition("speed");
		cnd1.addChild(cnd11);
		Condition cnd12 = new Condition("'$speed'");
		if(input.get("speed") != null){
			((Condition)cnd12).getProperties().put("speed", input.get("speed"));
		}
		cnd2.addChild(cnd12);
		Condition cnd13 = new Condition("course");
		cnd1.addChild(cnd13);
		Condition cnd14 = new Condition("'$course'");
		if(input.get("course") != null){
			((Condition)cnd14).getProperties().put("course", input.get("course"));
		}
		cnd2.addChild(cnd14);
		Condition cnd15 = new Condition("hdop");
		cnd1.addChild(cnd15);
		Condition cnd16 = new Condition("'$hdop'");
		if(input.get("hdop") != null){
			((Condition)cnd16).getProperties().put("hdop", input.get("hdop"));
		}
		cnd2.addChild(cnd16);
		Condition cnd17 = new Condition("time");
		cnd1.addChild(cnd17);
		Condition cnd18 = new Condition("'$time'");
		if(input.get("time") != null){
			((Condition)cnd18).getProperties().put("time", input.get("time"));
		}
		cnd2.addChild(cnd18);
		Condition cnd19 = new Condition("mcc");
		cnd1.addChild(cnd19);
		Condition cnd20 = new Condition("'$mcc'");
		if(input.get("mcc") != null){
			((Condition)cnd20).getProperties().put("mcc", input.get("mcc"));
		}
		cnd2.addChild(cnd20);
		Condition cnd21 = new Condition("mnc");
		cnd1.addChild(cnd21);
		Condition cnd22 = new Condition("'$mnc'");
		if(input.get("mnc") != null){
			((Condition)cnd22).getProperties().put("mnc", input.get("mnc"));
		}
		cnd2.addChild(cnd22);
		Condition cnd23 = new Condition("ssi");
		cnd1.addChild(cnd23);
		Condition cnd24 = new Condition("'$ssi'");
		if(input.get("ssi") != null){
			((Condition)cnd24).getProperties().put("ssi", input.get("ssi"));
		}
		cnd2.addChild(cnd24);
		Condition cnd25 = new Condition("gpsstatus");
		cnd1.addChild(cnd25);
		Condition cnd26 = new Condition("'$gpsstatus'");
		if(input.get("gpsstatus") != null){
			((Condition)cnd26).getProperties().put("gpsstatus", input.get("gpsstatus"));
		}
		cnd2.addChild(cnd26);
		Condition cnd27 = new Condition("cell");
		cnd1.addChild(cnd27);
		Condition cnd28 = new Condition("'$cell'");
		if(input.get("cell") != null){
			((Condition)cnd28).getProperties().put("cell", input.get("cell"));
		}
		cnd2.addChild(cnd28);
		Condition cnd29 = new Condition("alt");
		cnd1.addChild(cnd29);
		Condition cnd30 = new Condition("'$alt'");
		if(input.get("alt") != null){
			((Condition)cnd30).getProperties().put("alt", input.get("alt"));
		}
		cnd2.addChild(cnd30);
		Condition cnd31 = new Condition("lac");
		cnd1.addChild(cnd31);
		Condition cnd32 = new Condition("'$lac'");
		if(input.get("lac") != null){
			((Condition)cnd32).getProperties().put("lac", input.get("lac"));
		}
		cnd2.addChild(cnd32);
		Condition cnd33 = new Condition("label");
		cnd1.addChild(cnd33);
		Condition cnd34 = new Condition("'$label'");
		if(input.get("label") != null){
			((Condition)cnd34).getProperties().put("label", input.get("label"));
		}
		cnd2.addChild(cnd34);
		Condition cnd35 = new Condition("distance");
		cnd1.addChild(cnd35);
		Condition cnd36 = new Condition("'$distance'");
		if(input.get("distance") != null){
			((Condition)cnd36).getProperties().put("distance", input.get("distance"));
		}
		cnd2.addChild(cnd36);
		Condition cnd37 = new Condition("gaslevel");
		cnd1.addChild(cnd37);
		Condition cnd38 = new Condition("'$gaslevel'");
		if(input.get("gaslevel") != null){
			((Condition)cnd38).getProperties().put("gaslevel", input.get("gaslevel"));
		}
		cnd2.addChild(cnd38);
		if(!cnd0.toString().equals("")){
			buf.append(" " + cnd0.toString());
		}
		
		return buf.toString();
	}
	public void add(Connection con, Hashtable input) throws jp.crossfire.framework.database.HyperDbException {
		String sql = addSql(input);
		SQLExecutor exec = new SQLExecutor();
		exec.setCon(con);
		
		exec.execUpdateSQL(sql);
	}
	public String getLastSql(Hashtable input, Integer limit, Integer offset) {
		StringBuffer buf = new StringBuffer();
		
		buf.append("SELECT ");
		buf.append("*");
		
		buf.append(" FROM ");
		buf.append("gpsdata");
		
		// Condition
		ICondition condition = getLastGetCondition(input);
		if(condition != null && !condition.toString().equals("")){
			buf.append(" WHERE " + condition.toString());
		}
		
		// GROUP BY
		
		// Having Condition
		condition = getLastGetHavingCondition(input);
		if(condition != null && !condition.toString().equals("")){
			buf.append(" HAVING " + condition.toString());
		}
		
		// ORDER
		
		// Limit
		if(limit != null && offset != null){
			buf.append(" LIMIT " + offset.toString() +", " + limit.toString());
		}
		
		return buf.toString();
	}
	public List getLast(Connection con, Hashtable input, Integer limit, Integer offset) throws jp.crossfire.framework.database.HyperDbException {
		this.reset();
		String sql = getLastSql(input, limit, offset);
		SQLExecutor exec = new SQLExecutor();
		exec.setCon(con);
		
		// Listners
		List listners = new LinkedList();
		
		GpsdataManagerGetLastListner listner0 = (new GpsdataManagerGetLastListner());
		listners.add(listner0);
		
		// Execute Query
		exec.executeSelectSQL(sql, listners);
		listner0.orMap();
		
		this.gpsdata = listner0.gpsdataTmpCache;
		return listner0.result;
	}
	public void reset() {
			this.gpsdata.clear();
	}
}
